import 'package:flutter/foundation.dart';
import 'package:sqflite/orm/table.dart';
import 'package:sqflite/sqflite.dart';

class Migration {

  final Map<String, String> _tables = {};

  ///查表结构数据, 在 [upgradeTable] 之前执行
  Future queryTable(Transaction txn) {
    return txn.query('sqlite_master', columns: ['name', 'sql'], where: 'type = ?', whereArgs: ['table']).then((value) {
      for (var element in value) {
          final name = element['name'] as String;
          final value = element['sql'] as String;
          _tables[name] = value;
      }
    });
  }

  bool _checkTable(String oldTableSql, String newTableSql) {
    if(oldTableSql == newTableSql) return true;
    final oldT = oldTableSql.substring(oldTableSql.indexOf('('));
    final newT = newTableSql.substring(newTableSql.indexOf('('));
    return oldT == newT;
  }
  ///创建表
  Future createTable(Transaction txn, Table table) async {
    final tableSql = table.createTable();
    _printLog('create sql: $tableSql');
    await txn.execute(tableSql);
  }

  ///检查是否要升级表
  Future upgradeTable(Transaction txn, Table table) async {
    final tableName = table.tableName;
    final tableSql = table.createTable();
    final oldCode = _tables[tableName];
    if(oldCode == null) {
      _printLog('create sql: $tableSql');
      await txn.execute(tableSql);
      return Future.value();
    }

    if(_checkTable(oldCode, tableSql)) return Future.value();
    _printLog('Migration alter table $tableName');
    return _alterTable(txn, table, tableSql);
  }

  Future _alterTable(Transaction txn, Table table, String tableSql) async {
    final tableName = table.tableName;
    final temporaryName = 'tmp_for_copy_$tableName';

    //Step 1：创建临时表
    final tmpTablSql = tableSql.replaceFirst(tableName, temporaryName);
    _printLog('Tmp table sql: $tmpTablSql');
    await txn.execute(tmpTablSql);

    //Step 2：查找旧表字段
    final columns = await txn.rawQuery('PRAGMA TABLE_INFO($tableName)').then((value){
      final map = <String,String>{};
      for (var element in value) {
        map[element['name'] as String] = element['type'] as String;
      }
      return map;
    });

    //Step 3：数据迁移
    var first = true;
    final selectColumn = StringBuffer();
    for (var column in table.columns) {
      if (!columns.containsKey(column.name)) continue;
      if(column.type !=  columns[column.name]) continue;

      if (!first) selectColumn.write(', ');
      selectColumn.write(column.name);
      first = false;
    }
    final buffer = StringBuffer('INSERT INTO $temporaryName (');
    buffer.write(selectColumn);
    buffer.write(') SELECT ');
    buffer.write(selectColumn);
    buffer.write(' FROM $tableName');
    final newSql = buffer.toString();
    _printLog('alter table sql: $newSql');
    await txn.execute(newSql);

    //Step 4：删除旧表
    await txn.execute('DROP TABLE $tableName');

    //Step 5：修改表名
    await txn.execute('ALTER TABLE $temporaryName RENAME TO $tableName');
  }

  void _printLog(String msg) {
    if(kDebugMode) print(msg);
  }
}